/**
 * 
 */
package com.yidu.finance.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.yidu.finance.model.Finance;
import com.yidu.finance.utils.JDBCTools;

/**
 * 描述：理财dao
 * @author zhouyuxian
 * @date 2019年9月23日 下午7:30:21
 * @ version 1.0
 * 
 */
public class FinanceDao {
	
	Logger logger = Logger.getLogger(getClass());
	
	/**
	 * 添加理财
	 * @param finance
	 * @return
	 */
	public int insert(Finance finance){
		int rows = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = JDBCTools.getCon();
			String sql = "insert into finance values(null,?,?,?,?)";
			pstm = con.prepareStatement(sql);
			pstm.setInt(1, finance.getTypeID());
			pstm.setDouble(2, finance.getFinanceAmount());
			pstm.setString(3, finance.getFinanceRemarks());
			pstm.setInt(4,finance.getMemberId());
			rows = pstm.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCTools.close(con, pstm);
		}
		return rows;
	}
	
	/**
	 * 按ID删除理财
	 * @param financeID
	 * @return
	 */
	public int delete(int financeID){
		int rows = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = JDBCTools.getCon();
			String sql = "delete from finance where finance_id=?";
			pstm = con.prepareStatement(sql);
			pstm.setInt(1, financeID);
			rows = pstm.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCTools.close(con, pstm);
		}
		return rows;
	}
	
	/**
	 * 修改理财
	 * @param finance
	 * @return
	 */
	public int update(Finance finance){
		int rows = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = JDBCTools.getCon();
			String sql = "update finance set type_id=?,finance_amount=?"
					+ ",finance_remarks=?,member_id=? where finance_id=?" ;
			pstm = con.prepareStatement(sql);
			pstm.setInt(1, finance.getTypeID());
			pstm.setDouble(2, finance.getFinanceAmount());
			pstm.setString(3, finance.getFinanceRemarks());
			pstm.setInt(4, finance.getMemberId());
			pstm.setInt(5, finance.getFinanceID());
			rows = pstm.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCTools.close(con, pstm);
		}
		return rows;
	}
	
	/**
	 * 查询全部理财
	 * @return
	 */
	public List<Finance> selectAll(Integer mid,String typeId){
		List<Finance> list = new ArrayList<Finance>();
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			con = JDBCTools.getCon();
			
			String sql = "SELECT f.*,ft.type_name,ft.type_flag, "
					+ " m.member_name "
					+ " FROM finance f,member m,finance_type ft "
					+ " WHERE f.member_id=m.member_id "
					+ " AND f.type_id=ft.type_id ";
			if(mid!=null){
				sql += " and m.member_id="+mid ;
			}
			if(typeId!=null && !"".equals(typeId)){
				sql += " and ft.type_id="+typeId ;
			}
			
			sql += " order by f.finance_id desc " ;
			logger.info(sql);
			pstm = con.prepareStatement(sql);
			rs = pstm.executeQuery();
			while(rs.next()){
				Finance finance = new Finance();
				
				
				
				finance.setFinanceID(rs.getInt("finance_id"));
				finance.setTypeID(rs.getInt("type_id"));
				finance.setFinanceAmount(rs.getDouble("finance_amount"));
				finance.setFinanceRemarks(rs.getString("finance_remarks"));
				finance.setMemberId(rs.getInt("member_id"));
				
				
				//附加的属性
				finance.setTypeName(rs.getString("type_name"));
				finance.setMemberName(rs.getString("member_name"));
				finance.setTypeFlag(rs.getInt("type_flag"));
				
				
				list.add(finance);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCTools.close(con, pstm, rs);
		}
		return list;
	}
	
	/**
	 * 按ID查询理财
	 * @param financeID
	 * @return
	 */
	public Finance selectID(int financeID){
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			con = JDBCTools.getCon();
			String sql = "select * from finance where finance_id=?";
			pstm = con.prepareStatement(sql);
			pstm.setInt(1, financeID);
			rs = pstm.executeQuery();
			while(rs.next()){
				Finance finance = new Finance();
				finance.setFinanceID(rs.getInt("finance_id"));
				finance.setTypeID(rs.getInt("type_id"));
				finance.setFinanceAmount(rs.getDouble("finance_amount"));
				finance.setFinanceRemarks(rs.getString("finance_remarks"));
				finance.setMemberId(rs.getInt("member_id"));
				
				return finance;
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCTools.close(con, pstm, rs);
		}
		return null;
	}
}
